#!/bin/bash
function mysql_pre_set() {
    echo "安装MySQL" >>$logs_file
    mysqlPid=`pidof mysqld`
    mysqlFile=`ls /var/lib/mysql/ 2>/dev/null`
    if [ -n "$mysqlPid" -o -n "$mysqlFile" ];then
        whiptail --backtitle "Author: Elvis" --title "警告" --yesno \
               "检查到系统已经存在MySQL，继续则会删除之前的安装，是否继续？" 10 60
        if [ "$?" != "0" ];then
           sleep 0.15
           return 1
        fi
    fi
    mysql_ins_type=
    mysql_ins_ver=
    mysql_charset=
    mysql_passwd=
    mysql_lower_case=
    mysql_ins_type=$(whiptail --backtitle "Author: Elvis" --title "安装方式" --radiolist \
    "请选择安装方式？" 12 60 2 \
    "1" "离线安装                                " ON \
    "2" "在线安装" OFF 3>&1 1>&2 2>&3)
    if [ "$?" != "0" ];then
        return 1
    fi
    if [ "$die_network" == "true" -a "$mysql_ins_type" == "2" ];then
        whiptail --backtitle "Author: Elvis" --title "警告" --msgbox "检测到你的系统并未联网，请使用离线方式安装MySQL！！！" 10 70
        return 1
    fi
    
#    if [ "$mysql_ins_type" == "2" ];then
#        mysql_yum_repo=$(whiptail --backtitle "Author: Elvis" --nocancel --title "选择" --radiolist \
#    "请选择安装MySQL使用的YUM源？" 12 60 2 \
#    "1" "清华YUM源                                " ON \
#    "2" "官方YUM源" OFF 3>&1 1>&2 2>&3)
#    fi
    
    mysql_ins_ver=$(whiptail --backtitle "Author: Elvis" --title "MySQL版本" --radiolist \
    "请选择需要安装的MySQL版本?" 12 60 3 \
    "1" "MySQL 5.6                               " ON \
    "2" "MySQL 5.7" OFF \
    "3" "MySQL 8.0" OFF 3>&1 1>&2 2>&3) && \
    mysql_charset=$(whiptail --backtitle "Author: Elvis" --title "数据库字符集设置" --radiolist \
    "请选择MySQL使用的字符集，建议使用UTF8MB4" 12 60 3 \
    "1" "UTF8                                    " OFF \
    "2" "UTF8MB4" ON \
    "3" "GBK" OFF 3>&1 1>&2 2>&3) && \
    mysql_passwd=$(whiptail --backtitle "Author: Elvis" --title "设置密码" --passwordbox "设置MySQL用户ROOT密码，密码至少6位" 10 60 3>&1 1>&2 2>&3) && \
    mysql_lower_case=$(whiptail --backtitle "Author: Elvis" --title "参数设置" --radiolist \
    "表名是否区分大小写?" 12 60 4 \
    "1" "区分                        " ON \
    "2" "不区分                       " OFF 3>&1 1>&2 2>&3) && \
    {
    if [ -z "$mysql_passwd" ] || [ ${#mysql_passwd} -lt 6 ];then
        random_pw=`strings /dev/urandom | grep -o '[[:alnum:]]' | head -n 4 | tr -d '\n'`
        mysql_passwd=MyPIo01_$random_pw
    else
        mysql_passwd=${mysql_passwd// /}
    fi
    
    if [ "$mysql_charset" == "1" ];then 
        mysql_char_set=utf8
    elif [ "$mysql_charset" == "2" ];then
        mysql_char_set=utf8mb4
    else
        mysql_char_set=gbk
    fi
    
    if [ "$mysql_lower_case" == "1" ];then
        mysql_lower_case=0
    else
        mysql_lower_case=1
    fi

    if [ "$mysql_ins_ver" == "1" ];then
        mysql_big_ver="MySQL5.6"
    elif [ "$mysql_ins_ver" == "2" ];then
        mysql_big_ver="MySQL5.7"
    else
        mysql_big_ver="MySQL8.0"
    fi
    }
}


function mysql_env_check() {
    echo 1
    echo "将会安装$mysql_big_ver" >>$logs_file
    sleep 1
    msg="安装环境检测"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    
    if [ "$system_ver" == "8" ];then
        if [ "$mysql_ins_ver" == "1" -o "$mysql_ins_ver" == "2" ];then
          whiptail --backtitle "Author: Elvis" --title "错误" --msgbox \
          "CentOS 8不支持使用RPM方式安装MySQL5.6及5.7版本！！！\n请使用源码编译方式手动安装！！！" 10 70
          echo -e "CentOS 8不支持使用RPM方式安装MySQL5.6及5.7版本！！！\n请使用源码编译方式手动安装！！！" >>$logs_file
          return 1
        fi
    fi
    echo "`date "+%Y-%m-%d %H:%M:%S"` 清理之前的安装环境及rpm包" >>$logs_file
    echo -e "XXX\n10\n$msg\nXXX"
    echo 10
    service mysql stop>/dev/null 2>&1 || service mysqld stop>/dev/null 2>&1
    killall mysqld>/dev/null 2>&1
    rpm -qa |grep -i mysql-|xargs -t rpm -e --nodeps >>$logs_file 2>&1
    rpm -qa |grep -i mariadb|xargs -t rpm -e --nodeps >>$logs_file 2>&1
    if [ -d /var/lib/mysql ];then
      /bin/mv -f /var/lib/mysql /var/lib/mysql.bak$RANDOM>/dev/null
    fi
    mv -f /etc/my.cnf /etc/my.cnf.bak>/dev/null 2>&1
    mv -f /usr/my.cnf /usr/my.cnf.bak>/dev/null 2>&1
    rm -rf ~/.mysql_secret>/dev/null 2>&1
}
    
function mysql_para_set() {
    if [ -f /etc/my.cnf ];then
        my_conf=/etc/my.cnf
    elif [ -f /usr/my.cnf ];then
        my_conf=/usr/my.cnf
    else
        echo "未找到my.cnf文件，请确定MySQL-server软件包是否安装" >>$logs_file
        return 1
    fi
    
    msg="设置MySQL参数"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    echo -e "XXX\n50\n$msg\nXXX"
    echo 50
    
    attach_point=`cat $my_conf |grep -E "\[*\]$"|grep -v "#"|grep -v "\[mysqld\]"|head -n1`
    attach_point=${attach_point//\[/\\[}
    attach_point=${attach_point//\]/\\]}
    #如果有其他[]标签，则保存my.cnf自标签后面的配置参数
    if [ -z "$attach_point" ];then
        attach_point="none_attach_point"
    fi
    conf_part=`sed -n "/$attach_point/,\$"p $my_conf`
    sed -i "/$attach_point/,\$d" $my_conf
    
    all_mysql_conf="character-set-server = $mysql_char_set
lower_case_table_names = $mysql_lower_case
transaction_isolation = READ-COMMITTED

log-bin = mysql-bin
server-id=1
binlog_format = row
expire_logs_days = 7
max_binlog_size = 200M 
log-bin-trust-function-creators = 1

skip-external-locking
skip-name-resolve
skip-slave-start
skip-host-cache

wait_timeout = 86400
interactive_timeout = 86400
max_connections = 5000
max_allowed_packet = 100M

[client]
default-character-set = $mysql_char_set"
    
    #删除原有的配置参数
    mysql_conf_key=(${all_mysql_conf// = /=})
    for i in $mysql_conf_key
    do
      conf_key=`echo ${mysql_conf_key[$n]}|awk -F"=" '{print $1}'`
      sed -i "/$conf_key/d" $my_conf
    done
    
    # 设置MySQL密码策略
    if [ "$mysql_ins_ver" == "2" ];then
        echo -e "\n#validate_password_policy = 0\n#validate_password_length = 6\n" >>$my_conf
    elif [ "$mysql_ins_ver" == "3" ];then
        echo -e "\n#validate_password.policy = 0\n#validate_password.length = 6\n" >>$my_conf
    fi
    
    #添加新的配置
    cat >>$my_conf<<EOF
$all_mysql_conf
    
$conf_part
EOF
    
    echo -e "XXX\n60\n初始化MySQL\nXXX"
    echo 60
    service mysqld restart>/dev/null 2>&1|| service mysql restart>/dev/null 2>&1
    sleep 1
    n=1
    ibdata_tm=`ls -l --time-style=full-iso|grep ibdata1|awk '{print $7}'`
    ibdata_tmc=0
    while [ "$ibdata_tm" != "$ibdata_tmc" ]
    do
      sleep 0.30
      ibdata_tm=`ls -l --time-style=full-iso|grep ibdata1|awk '{print $7}'`
      echo "等待MySQL初始化 ${n} ....." >>$logs_file 2>&1
      if [ $n -gt 40 ];then
        break
      fi
      sleep 1
      ibdata_tmc=`ls -l --time-style=full-iso|grep ibdata1|awk '{print $7}'`
      let n+=1
    done
    sleep 2
    msg="设置MySQL密码"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    echo -e "XXX\n70\n$msg\nXXX"
    echo 70
    if [ "$system_ver" != "6" -a "$mysql_ins_ver" == "2" ];then
        systemctl stop mysqld >/dev/null 2>&1
        systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" >/dev/null 2>&1
        systemctl start mysqld >/dev/null 2>&1
        systemctl unset-environment MYSQLD_OPTS >/dev/null 2>&1
    else
        sed -i "/\[mysqld\]/a skip-grant-tables" $my_conf
        service mysqld restart>/dev/null 2>&1|| service mysql restart>/dev/null 2>&1
    fi
    sed -i 's/.*\(validate_password.*\)/\1/g' $my_conf
    sleep 1
    
    n=1
    mysql_status=`pidof mysqld`
    while [ -z "$mysql_status" ]
    do
      echo "等待MySQL启动"
      if [ $n -gt 25 ];then
        break
      fi
      sleep 1
      let n+=1
      mysql_status=`pidof mysqld`
    done
    
    mysql -uroot -e "update mysql.user set password_expired='N' where user='root';" >>$logs_file 2>&1
    if [ "$mysql_ins_ver" == "2" ];then
        mysql -uroot -e "update mysql.user set authentication_string=password('$mysql_passwd') where user='root';" >>$logs_file 2>&1
    elif [ "$mysql_ins_ver" == "3" ];then
        mysql -uroot -e "update mysql.user set authentication_string='' where user='root';" >>$logs_file 2>&1
    else
        mysql -uroot -e "UPDATE mysql.user SET Password = PASSWORD('$mysql_passwd') WHERE User = 'root';" >>$logs_file 2>&1
    fi
    
    sed -i "/skip-grant-tables/d" $my_conf
    
    msg="启动MySQL"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    echo -e "XXX\n80\n$msg\nXXX"
    echo 80
    service mysql restart >/dev/null 2>&1|| service mysqld restart >/dev/null 2>&1
    mysql_status=`pidof mysqld`
    while [ -z "$mysql_status" ]
    do
      echo "等待MySQL启动"
      if [ $n -gt 25 ];then
        break
      fi
      sleep 1
      let n+=1
      mysql_status=`pidof mysqld`
    done
    msg="开启ROOT用户远程访问"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    echo -e "XXX\n90\n$msg\nXXX"
    echo 90
    if [ "$mysql_ins_ver" == "3" ];then
        mysql -uroot -e "ALTER user 'root'@'localhost' IDENTIFIED BY '$mysql_passwd';" >>$logs_file 2>&1
        mysql -uroot -p$mysql_passwd -e "flush privileges;" >>$logs_file 2>&1
        mysql -uroot -p$mysql_passwd -e "CREATE USER 'root'@'%' IDENTIFIED BY '$mysql_passwd';" >>$logs_file 2>&1
        mysql -uroot -p$mysql_passwd -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;" >>$logs_file 2>&1
        #mysql -uroot -p$mysql_passwd -e "update mysql.user set host='%' where user ='root';" >>$logs_file 2>&1
        
    else
        mysql -uroot -p$mysql_passwd -e "grant all privileges on *.* to root@'%' identified by '$mysql_passwd';" >>$logs_file 2>&1
    fi
    mysql -uroot -p$mysql_passwd -e "flush privileges;" >>$logs_file 2>&1
    sleep 1.10
    echo 100
    sleep 0.30
}

function mysql_statu_check() {
    mysql_ver=`rpm -qa|grep "mysql"|grep "server"|grep -Eo "[0-9]+\.[0-9]+\.[0-9]+"`
    mysql_is_run=`pidof mysqld`
    if [ -n "$mysql_is_run" ];then
        echo "已经安装完成MySQL $mysql_ver" >>$logs_file
        mysql -uroot -p$mysql_passwd -e "show databases;" >/dev/null 2>&1
        if [ "$?" == "0" ];then
          if (whiptail --backtitle "Author: Elvis" --title "提示" --yesno \
          "MySQL $mysql_ver安装成功，默认密码为$mysql_passwd\n\n是否需要将密码保存到文件？如果是，密码将已BASE64加密\n方式保存到文件/root/.MYDBPW\n\n查看密码命令：\ncat /root/.MYDBPW |base64 -d|cut -b 7- " 14 60) then
            echo "env.sh$mysql_passwd" |base64>/root/.MYDBPW
            echo -e "密码保存在/root/.MYDBPW，base64解密后去掉env.sh即密码\n或者使用命令 cat /root/.MYDBPW |base64 -d|cut -b 7- 查看" >>$logs_file
          fi
        else
          echo "MySQL设置密码失败，可能因为MySQL版本变化，导致修改密码失败，请参考官方文档重置密码！！" >>$logs_file
          whiptail --backtitle "Author: Elvis" --title "警告" --msgbox "MySQL安装成功，但是设置ROOT密码失败\n请参考官方文档重置密码" 10 70
        fi
        sleep 0.5
    else
        echo "MySQL安装失败，请检查日志文件" >>$logs_file
        whiptail --backtitle "Author: Elvis" --title "警告" --msgbox "MySQL安装失败，请检查日志文件" 10 70
        sleep 0.5
    fi
    echo "" >>$logs_file
    echo "" >>$logs_file
}


function install_mysql_online() {
    #判断yum是否在运行
    yum_pid=`ps aux|grep "/usr/bin/yum"|grep -v grep|awk '{print $2}'`
    if [ -n "$yum_pid" ];then
        for pid in $yum_pid
        do
         kill $pid >/dev/null 2>&1
         sleep 1
         kill -9 $pid >/dev/null 2>&1
        done
    fi
    msg="添加MySQL YUM源"
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    echo -e "XXX\n20\n$msg\nXXX"
    echo 20
    if [ "$system_ver" == "6" ];then
        cat>/etc/yum.repos.d/mysql-community-qh.repo<<'EOF'
[mysql-connectors-community-qh]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el6-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-tools-community-qh]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el6-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.6-community-qh]
name=MySQL 5.6 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.6-community-el6-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.7-community-qh]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el6-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-8.0-community-qh]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el6-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
EOF
        rpm -ivh https://repo.mysql.com//mysql80-community-release-el6-3.noarch.rpm >/dev/null 2>&1
    elif [ "$system_ver" == "7" ];then
        cat>/etc/yum.repos.d/mysql-community-qh.repo<<'EOF'
[mysql-connectors-community-qh]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-tools-community-qh]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.6-community-qh]
name=MySQL 5.6 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.6-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.7-community-qh]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-8.0-community-qh]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
EOF
        rpm -ivh https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm >/dev/null 2>&1
    else
        cat>/etc/yum.repos.d/mysql-community-qh.repo<<'EOF'
[mysql-connectors-community-qh]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el8-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-tools-community-qh]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el8-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-8.0-community-qh]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el8-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql
EOF
        rpm -ivh https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm >/dev/null 2>&1
    fi
    
    #禁用不需要的mysql版本YUM源
    yum clean all >/dev/null 2>&1
    sed -i 's/enabled=1/enabled=0/g' /etc/yum.repos.d/mysql-community.repo >/dev/null 2>&1
    sed -i 's/enabled=1/enabled=0/g' /etc/yum.repos.d/mysql-community-qh.repo >/dev/null 2>&1
    if [ "$mysql_ins_ver" == "1" ];then
        sed -i '/mysql-5.6-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community.repo >/dev/null 2>&1
        sed -i '/mysql-5.6-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community-qh.repo >/dev/null 2>&1
    elif [ "$mysql_ins_ver" == "2" ];then
        sed -i '/mysql-5.7-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community.repo >/dev/null 2>&1
        sed -i '/mysql-5.7-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community-qh.repo >/dev/null 2>&1
    else
        sed -i '/mysql-8.0-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community.repo >/dev/null 2>&1
        sed -i '/mysql-8.0-community/{:a;n;s/enabled=.*/enabled=1/g;/gpgcheck/!ba}' /etc/yum.repos.d/mysql-community-qh.repo >/dev/null 2>&1
    fi    

    msg="开始安装，安装等待时间视网络情况而定"
    echo -e "XXX\n30\n$msg\nXXX"
    echo 30
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    sleep 1
    echo -e "XXX\n40\n正在安装中，请耐心等待.....\nXXX"
    yum -y install libaio perl numactl perl-Data-Dumper perl-Module-Install mysql-community-server >>$logs_file 2>&1
}

function install_mysql_offline() {
    msg="使用离线安装MySQL"
    echo -e "XXX\n30\n$msg\nXXX"
    echo 30
    echo "`date "+%Y-%m-%d %H:%M:%S"` $msg" >>$logs_file
    sleep 1
    
    if [ "$mysql_ins_ver" == "1" ];then
        mysql_ver=`ls -r $workHome/packages/mysql/ 2>/dev/null|grep -i MySQL-5.6|head -n1|awk -F"-" '{print $2}'`
    elif [ "$mysql_ins_ver" == "2" ];then
        mysql_ver=`ls -r $workHome/packages/mysql/ 2>/dev/null|grep -i MySQL-5.7|head -n1|awk -F"-" '{print $2}'`
    elif [ "$mysql_ins_ver" == "3" ];then
        mysql_ver=`ls -r $workHome/packages/mysql/ 2>/dev/null|grep -i MySQL-8.0|head -n1|awk -F"-" '{print $2}'`
    fi
    
    echo -e "XXX\n40\n正在安装中，请耐心等待.......\nXXX"
    echo 40
    yum -y install libaio perl numactl perl-Data-Dumper perl-Module-Install >>$logs_file 2>&1
    yum localinstall -y $workHome/packages/mysql/MySQL-${mysql_ver}/el${system_ver}/*.rpm >>$logs_file 2>&1
}